<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="bb137420">ALTER TYPE</title><body><p id="sql_command_desc">Changes the definition of a data type.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">
ALTER TYPE <varname>name</varname> <varname>action</varname> [, ... ]
ALTER TYPE <varname>name</varname> OWNER TO <varname>new_owner</varname>
ALTER TYPE <varname>name</varname> RENAME ATTRIBUTE <varname>attribute_name</varname> TO <varname>new_attribute_name</varname> [ CASCADE | RESTRICT ]
ALTER TYPE <varname>name</varname> RENAME TO <varname>new_name</varname>
ALTER TYPE <varname>name</varname> SET SCHEMA <varname>new_schema</varname>
ALTER TYPE <varname>name</varname> ADD VALUE [ IF NOT EXISTS ] <varname>new_enum_value</varname> [ { BEFORE | AFTER } <varname>existing_enum_value</varname> ]
ALTER TYPE <varname>name</varname> SET DEFAULT ENCODING ( <varname>storage_directive</varname> )

where <varname>action</varname> is one of:
  
  ADD ATTRIBUTE <varname>attribute_name</varname> <varname>data_type</varname> [ COLLATE <varname>collation</varname> ] [ CASCADE | RESTRICT ]
  DROP ATTRIBUTE [ IF EXISTS ] <varname>attribute_name</varname> [ CASCADE | RESTRICT ]
  ALTER ATTRIBUTE <varname>attribute_name</varname> [ SET DATA ] TYPE <varname>data_type</varname> [ COLLATE <varname>collation</varname> ] [ CASCADE | RESTRICT ]
</codeblock>
<p>where <varname>storage_directive</varname> is:</p>
      <codeblock>   COMPRESSTYPE={ZLIB | ZSTD | QUICKLZ | RLE_TYPE | NONE}
   COMPRESSLEVEL={0-19}
   BLOCKSIZE={8192-2097152}</codeblock></section>
    <section id="section3"><title>Description</title><p><codeph>ALTER TYPE</codeph> changes the
        definition of an existing type. There are several subforms:</p>
      <ul>
        <li>
          <b><codeph>ADD ATTRIBUTE</codeph></b> — Adds a new attribute to a composite
          type, using the same syntax as <codeph>CREATE TYPE</codeph>. </li>
        <li>
          <b><codeph>DROP ATTRIBUTE [ IF EXISTS ]</codeph></b> — Drops an attribute from a
          composite type. If <codeph>IF EXISTS</codeph> is specified and the attribute does not
          exist, no error is thrown. In this case a notice is issued instead. </li>
        <li><b><codeph>SET DATA TYPE</codeph></b> — Changes the type of an attribute of a
          composite type. </li>
        <li>
          <b><codeph>OWNER</codeph></b> — Changes the owner of the type. </li>
        <li>
          <b><codeph>RENAME</codeph></b> — Changes the name of the type or the name of an
          individual attribute of a composite type. </li>
        <li>
          <b><codeph>SET SCHEMA</codeph></b> — Moves the type into another schema. </li>
        <li>
          <b><codeph>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</codeph></b> — Adds a new value
          to an enum type. The new value's place in the enum's ordering can be specified as being
            <codeph>BEFORE</codeph> or <codeph>AFTER</codeph> one of the existing values. Otherwise,
          the new item is added at the end of the list of values. <p>If <codeph>IF NOT
              EXISTS</codeph> is specified, it is not an error if the type already contains the new
            value; a notice is issued but no other action is taken. Otherwise, an error will occur
            if the new value is already present.</p>
        </li>
        <li>
          <b><codeph>CASCADE</codeph></b> — Automatically propagate the operation to typed tables of
          the type being altered, and their descendants. </li>
        <li>
          <b><codeph>RESTRICT</codeph></b> — Refuse the operation if the type being altered is the
          type of a typed table. This is the default. </li>
      </ul>
      <p> The <codeph>ADD ATTRIBUTE</codeph>, <codeph>DROP ATTRIBUTE</codeph>, and <codeph>ALTER
          ATTRIBUTE</codeph> actions can be combined into a list of multiple alterations to apply in
        parallel. For example, it is possible to add several attributes and/or alter the type of
        several attributes in a single command. </p>
      <p>You can change the name, the owner, and the schema of a type. You can also add or update
        storage options for a scalar type.<note>Greenplum Database does not support adding storage
          options for row or composite types.</note></p><p>You must own the type to use <codeph>ALTER TYPE</codeph>. To change the schema of a type, you
        must also have <codeph>CREATE</codeph> privilege on the new schema. To alter the owner, you
        must also be a direct or indirect member of the new owning role, and that role must have
          <codeph>CREATE</codeph> privilege on the type's schema. (These restrictions enforce that
        altering the owner does not do anything that could be done by dropping and recreating the
        type. However, a superuser can alter ownership of any type.) To add an attribute or alter an attribute type, you must also
        have <codeph>USAGE</codeph> privilege on the data type.</p>
      <p><codeph>ALTER TYPE ... ADD VALUE</codeph> (the form that adds a new value to an enum type)
        cannot be run inside a transaction block.</p>
      <p> Comparisons involving an added enum value will sometimes be slower than comparisons
        involving only original members of the enum type. This will usually only occur if
          <codeph>BEFORE</codeph> or <codeph>AFTER</codeph> is used to set the new value's sort
        position somewhere other than at the end of the list. However, sometimes it will happen even
        though the new value is added at the end (this occurs if the OID counter "wrapped around"
        since the original creation of the enum type). The slowdown is usually insignificant; but if
        it matters, optimal performance can be regained by dropping and recreating the enum type, or
        by dumping and reloading the database. </p>
    </section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name (optionally schema-qualified) of an existing type to alter.
</pd></plentry><plentry><pt><varname>new_name</varname></pt><pd>The new name for the type.</pd></plentry><plentry><pt><varname>new_owner</varname></pt><pd>The user name of the new owner of the type. </pd></plentry><plentry><pt><varname>new_schema</varname></pt><pd>The new schema for the type. </pd></plentry>
          <plentry>
            <pt><varname>attribute_name</varname></pt>
            <pd>The name of the attribute to add, alter, or drop.            
            </pd>
          </plentry>         
          <plentry>
            <pt><varname>new_attribute_name</varname></pt>
            <pd>The new name of the attribute to be renamed.             
            </pd>
          </plentry>         
          <plentry>
            <pt><varname>data_type</varname></pt>
            <pd>The data type of the attribute to add, or the new type of the
                attribute to alter.             
            </pd>
          </plentry>         
          <plentry>
            <pt><varname>new_enum_value</varname></pt>
            <pd>The new value to be added to an enum type's list of values.
                Like all enum literals, it needs to be quoted.             
            </pd>
          </plentry>         
        <plentry>
          <pt><varname>existing_enum_value</varname></pt>
          <pd>The existing enum value that the new value should be added immediately before or after
            in the enum type's sort ordering. Like all enum literals, it needs to be quoted. </pd>
        </plentry>
         <plentry><pt><varname>storage_directive</varname></pt><pd>Identifies default storage options for the type when specified in a table column definition. Options include <codeph>COMPRESSTYPE</codeph>, <codeph>COMPRESSLEVEL</codeph>, and <codeph>BLOCKSIZE</codeph>.</pd><pd><b>COMPRESSTYPE</b> — Set to <codeph>ZLIB</codeph> (the default), <codeph>ZSTD</codeph>,
              <codeph>RLE_TYPE</codeph>, or <codeph>QUICKLZ</codeph><sup>1</sup> to specify the type
              of compression used.<note type="note"><sup>1</sup>QuickLZ compression is available only in the commercial
                release of Tanzu Greenplum.</note></pd><pd><b>COMPRESSLEVEL</b> — For Zstd compression, set to an
              integer value from 1 (fastest compression) to 19 (highest compression ratio).
              For zlib compression, the valid range is from 1 to 9. The QuickLZ
            compression level can only be set to 1. For
              <codeph>RLE_TYPE</codeph>, the compression level can be set to an integer value from 1
            (fastest compression) to 4 (highest compression ratio). The default compression level is 1.</pd>
          <pd><b>BLOCKSIZE</b> — Set to the size, in bytes, for each block in the column. The
              <codeph>BLOCKSIZE</codeph> must be between 8192 and 2097152 bytes, and be a multiple
            of 8192. The default block size is 32768.<note><varname>storage_directives</varname> defined at the table- or column-level override the default storage options defined for a type.</note></pd></plentry></parml></section>
    <section id="section5"><title>Examples</title><p>To rename the data type named
          <codeph>electronic_mail</codeph>:
        </p><codeblock>ALTER TYPE electronic_mail RENAME TO email;</codeblock><p>To change the owner
        of the user-defined type <codeph>email</codeph> to <codeph>joe</codeph>:
        </p><codeblock>ALTER TYPE email OWNER TO joe;</codeblock><p>To change the schema of the
        user-defined type <codeph>email</codeph> to <codeph>customers</codeph>:
        </p><codeblock>ALTER TYPE email SET SCHEMA customers;</codeblock><p>To set or alter the
        compression type and compression level of the user-defined type named
        <codeph>int33</codeph>: </p><codeblock>ALTER TYPE int33 SET DEFAULT ENCODING (compresstype=zlib, compresslevel=7);</codeblock>
      <p> To add a new attribute to a type:</p>
      <codeblock>ALTER TYPE compfoo ADD ATTRIBUTE f3 int;</codeblock>
      <p> To add a new value to an enum type in a particular sort position:</p>
      <codeblock>ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';</codeblock>
    </section><section id="section6"><title>Compatibility</title><p>The variants to add and drop attributes are part of the SQL standard; the other variants are Greenplum Database extensions.</p></section><section id="section7"><title>See Also</title><p><codeph><xref href="./CREATE_TYPE.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="./DROP_TYPE.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
